<?php
require_once 'vendor/autoload.php';

use Illuminate\Database\Capsule\Manager as Capsule;

$capsule = new Capsule;
$capsule->addConnection([
    'driver'    => 'mysql',
    'host'      => '129.226.194.102',
    'database'  => 'aimeos_lvgang_to',
    'username'  => 'aimeos_lvgang_to',
    'password'  => 'ZQaJw6X9nksn8kt7',
    'charset'   => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix'    => '',
]);

$capsule->setAsGlobal();
$capsule->bootEloquent();

try {
    $pdo = $capsule->getConnection()->getPdo();
    
    // Create mshop_locale_language table
    $sql = "CREATE TABLE IF NOT EXISTS mshop_locale_language (
        id VARCHAR(5) NOT NULL PRIMARY KEY,
        label VARCHAR(255) NOT NULL,
        status SMALLINT NOT NULL DEFAULT 1,
        mtime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        ctime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        editor VARCHAR(255) NOT NULL DEFAULT '',
        KEY idx_mslocla_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
    
    $pdo->exec($sql);
    echo "mshop_locale_language table created successfully\n";
    
    // Create mshop_locale_currency table
    $sql = "CREATE TABLE IF NOT EXISTS mshop_locale_currency (
        id VARCHAR(3) NOT NULL PRIMARY KEY,
        label VARCHAR(255) NOT NULL,
        status SMALLINT NOT NULL DEFAULT 1,
        mtime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        ctime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        editor VARCHAR(255) NOT NULL DEFAULT '',
        KEY idx_msloccu_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
    
    $pdo->exec($sql);
    echo "mshop_locale_currency table created successfully\n";
    
    // Update mshop_locale table to add site_id column
    $sql = "ALTER TABLE mshop_locale ADD COLUMN IF NOT EXISTS site_id INT DEFAULT NULL;";
    $pdo->exec($sql);
    echo "mshop_locale table updated with site_id column\n";
    
    // Insert default language data
    $insertSql = "INSERT IGNORE INTO mshop_locale_language (id, label, status, editor) VALUES 
        ('en', 'English', 1, 'setup'),
        ('zh', 'Chinese', 1, 'setup');";
    
    $pdo->exec($insertSql);
    echo "Default language data inserted successfully\n";
    
    // Insert default currency data
    $insertSql = "INSERT IGNORE INTO mshop_locale_currency (id, label, status, editor) VALUES 
        ('USD', 'US Dollar', 1, 'setup'),
        ('CNY', 'Chinese Yuan', 1, 'setup');";
    
    $pdo->exec($insertSql);
    echo "Default currency data inserted successfully\n";
    
    // Update mshop_locale with site_id
    $updateSql = "UPDATE mshop_locale SET site_id = (SELECT id FROM mshop_locale_site WHERE code = 'default' LIMIT 1) WHERE site_id IS NULL;";
    $pdo->exec($updateSql);
    echo "mshop_locale updated with site_id\n";
    
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}